package db;

import java.sql.*;
import java.util.ArrayList;

public class DatabaseManager {

	Connection con = null;
	Statement stmt;
	PreparedStatement inDoc, inPar;
	
	public DatabaseManager()
	{
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection("jdbc:mysql://bailz.dyndns.org/project","Foxtrot","cs3015");
			stmt = con.createStatement();
			inDoc = con.prepareStatement("INSERT INTO essay (subject, author) VALUES (?,?)");
			inPar = con.prepareStatement("INSERT INTO paragraph (eid, content) VALUES (?,?)");
		} catch(Exception e){		
				//Couldn't connect to the database.
				System.err.println("Failed to connect to the database!");
				e.printStackTrace();
		}
	}
	
	/**
	 * Inserts a new document into the database.
	 * @param essay The essay to be inserted
	 * @return A success value
	 */
	public boolean insertDocument(app.Essay essay)
	{
		//Insert the subject and author;
		ResultSet rs;
		int id = 0;
		try{
			inDoc.setString(1,essay.subject);
			inDoc.setString(2,essay.author);
			inDoc.executeUpdate();
			rs = stmt.executeQuery("SELECT id FROM essay ORDER BY id DESC LIMIT 0,1");
			if(rs.first())
				id = rs.getInt(1);
			//Insert the paragraphs
			for(int i=0;i<essay.paragraphs.size();i++){
				inPar.setInt(1,id);
				inPar.setString(2,essay.paragraphs.get(i));
				inPar.executeUpdate();
			}
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public ArrayList<String> getAllSubjects()
	{
		ArrayList<String> subjects = new ArrayList<String>();
		ResultSet rs;
		try{
			rs = stmt.executeQuery("SELECT subject FROM essay GROUP BY subject");
			if(rs.first()){
				while(rs.next()){
					subjects.add(rs.getString(1));
				}
				return subjects;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return subjects;
	}
	
	public ArrayList<app.Essay> getEssays(String subject)
	{
		ArrayList<app.Essay> essays = new ArrayList<app.Essay>();
		ResultSet rs;
		try{
			rs = stmt.executeQuery("SELECT * FROM essay WHERE subject=\"" + subject + "\"");
			if(rs.first()){
				rs.beforeFirst();
				while(rs.next())
					essays.add(new app.Essay(rs.getInt(1),rs.getString(2),rs.getString(3),getContents(rs.getInt(1))));
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return essays;
	}
	
	public String getContents(int id)
	{
		String contents = "";
		ResultSet rs2;
		try {
			Statement temp = con.createStatement();
			rs2 = temp.executeQuery("SELECT content FROM paragraph WHERE eid=" + id);
			while(rs2.next())
				contents += rs2.getString(1);
		}catch(Exception e){
			e.printStackTrace();
		}
		return contents;
	}
}
